package extend.org.compiere.hrm;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.logging.Level;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class AverageSalary {
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (AverageSalary.class);
	
	private double AMOUNT;
	private double BONUS;
	private int HOURQUOTA;
	private int INDEX;
	private double RESULT;
	private Timestamp STARTDATE;
	private Timestamp ENDDATE;
	
	public Timestamp getSTARTDATE() {
		return STARTDATE;
	}

	public void setSTARTDATE(Timestamp sTARTDATE) {
		STARTDATE = sTARTDATE;
	}

	public Timestamp getENDDATE() {
		return ENDDATE;
	}

	public void setENDDATE(Timestamp eNDDATE) {
		ENDDATE = eNDDATE;
	}

	public int getINDEX() {
		return INDEX;
	}

	public void setINDEX(int iNDEX) {
		INDEX = iNDEX;
	}
		
	public double getAMOUNT() {
		return AMOUNT;
	}

	public void setAMOUNT(double aMOUNT) {
		AMOUNT = aMOUNT;
	}

	public double getBONUS() {
		return BONUS;
	}

	public void setBONUS(double bONUS) {
		BONUS = bONUS;
	}

	public double getRESULT() {
		return RESULT;
	}

	public void setRESULT(double rESULT) {
		RESULT = rESULT;
	}

	public int getHOURQUOTA() {
		return HOURQUOTA;
	}
	
	public void setHOURQUOTA(int hOURQUOTA) {
		HOURQUOTA = hOURQUOTA;
	}
		
	
public static BigDecimal getTotal(int C_BPartner_ID, Timestamp dt) {
	
	if(C_BPartner_ID == 0)
		return null;
			
	BigDecimal result = new BigDecimal(0);
	PreparedStatement pstmt = null;
	ResultSet rs = null;
			
		// Employee data from the card
	String sql_ = "SELECT TOTALIND FROM HRM_AVERAGESALARY_V \n" +
					" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + "\n" +
					" AND C_BPARTNER_ID="  + C_BPartner_ID + "\n" +
					" AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + "\n" +
					" AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + "\n" ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					return rs.getBigDecimal(1);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			
		return result;
		
	}

public static int getWorkdays(int C_BPartner_ID, Timestamp dt) {
	
	if(C_BPartner_ID == 0)
		return 0;
			
	int result = 0;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
			
		// Employee data from the card
	String sql_ = "SELECT WORKDAYS FROM HRM_AVERAGESALARY_V \n" +
					" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + "\n" +
					" AND C_BPARTNER_ID="  + C_BPartner_ID + "\n" +
					" AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + "\n" +
					" AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + "\n" ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();	
				if(rs.next())
					return rs.getInt(1);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			
		return result;
		
	}

public static int getIndex(int C_BPartner_ID, Timestamp dt) {
	
	if(C_BPartner_ID == 0)
		return 0;
			
	int result = 0;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
			
		// Employee data from the card
			String sql_ = "SELECT IND FROM HRM_AVERAGESALARY_V " +
							" WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + "\n" +
							" AND C_BPARTNER_ID="  + C_BPartner_ID + "\n" +
							" AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + "\n" +
							" AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + "\n" ;
			try
			{
				pstmt = DB.prepareStatement(sql_,null);				
				rs = pstmt.executeQuery();	
				if(rs.next())
					return rs.getInt(1);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return result;
	}

}
